package org.hadoop.hbase;

import org.apache.phoenix.jdbc.PhoenixDriver;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class PhoenixOperation {
    private Connection connection;

    @Before
    public void before() throws Exception {
        Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");
        String url = "jdbc:phoenix:server201:2181";
        connection = DriverManager.getConnection(url, "", "");
    }

    @After
    public void after() throws Exception {
        connection.close();
    }

    /**
     * 查询数据
     */
    @Test
    public void queryTest() throws Exception {
        Statement st = connection.createStatement();
        ResultSet rs = st.executeQuery("select * from books");
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println("id:" + id + ",name:" + name);
        }
        rs.close();
        st.close();
    }

    /**
     * 开始事务
     */
    @Test
    public void testTx() throws Exception {
        System.err.println("是否是自动提交："+connection.getAutoCommit());//默认值false
        if(connection.getAutoCommit()){
            connection.setAutoCommit(false);
        }
        String sql = "upsert into db1.stud(id,name) values(3,'Alex')";
        Statement st = connection.createStatement();
        st.execute(sql);
        connection.commit();//必须要手动提交
        connection.setAutoCommit(true);
        st.close();
        System.out.println("写入成功");
    }

    /**
     * 聚合查询
     */
    @Test
    public void testAgg() throws Exception{
        String sql = "select count(1) from db1.stud";
        Statement st = connection.createStatement();
        ResultSet rs = st.executeQuery(sql);
        if (rs.next()) {
            Long sum = rs.getLong(1);
            System.out.println("count is:" + sum);
        }
        rs.close();
        st.close();
    }
}
